--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_PAST_TERM_PRO_LOAF.HQL
--    Functions : 表31：贷款发生额分产品分期限统计表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_PAST_TERM_PRO_LOAF PARTITION (DATA_DATE = '#V_DATA_DATE#')
SELECT  AREA.AREA_CODE_4                 AS FIN_ORG_DIST
       ,T.CCY                            AS CCY
       ,T.BUSINESS_TYPE                  AS BUSINESS_TYPE     -- 贷款产品类型
       ,T.TYPE_DESC                      AS TYPE_DESC         -- 产品类型描述
       ,TERM.LOAN_TERM_ID                AS TERM_CODE         -- 期限代码
       ,TERM.LOAN_TERM_DSCR              AS TERM_DESC         -- 期限描述
       ,SUM(T.OCCUR_AMOUNT)/100000000    AS OCCUR_AMOUNT      -- 贷款余额
       ,SUM(T.AMOUNT_LM    )/100000000   AS AMOUNT_LM         -- 余额上期末
       ,SUM(T.AMOUNT_YS    )/100000000   AS AMOUNT_YS         -- 当年累计发生额
       ,SUM(T.AMOUNT_GROW  )/100000000   AS AMOUNT_GROW       -- 同比增长
       ,SUM(T.WSUM_AMOUNT  )/100000000   AS WSUM_AMOUNT       -- 余额加权值
       ,SUM(T.WAMOUNT_LM   )/100000000   AS WAMOUNT_LM        -- 余额加权值上期末
       ,SUM(T.WAMOUNT_YS   )/100000000   AS WAMOUNT_YS        -- 发生额加权值当年累计
       ,SUM(T.WAMOUNT_LC   )/100000000   AS WAMOUNT_LC        -- 余额加权值去年同期
FROM (
    -- 个人+机构
    SELECT
     FSE.FIN_ORG_NO                                                          AS FIN_ORG_NO
    ,FSE.CCY                                                                 AS CCY
    ,FSE.CLIENT_TYPE                                                         AS BUSINESS_TYPE   -- 产品类型
    ,CASE WHEN FSE.CLIENT_TYPE='1' THEN '个人'
          WHEN FSE.CLIENT_TYPE='0' THEN '机构' END                           AS TYPE_DESC       -- 产品类别描述
    ,FSE.TERM_CODE                                                           AS TERM_CODE       -- 期限代码
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219','F02201','F02202','F02203','F023','F081','F082','F09')
          AND DATA_DATE='#V_DATA_DATE#') FSE

    UNION ALL

    -- 个人_普通贷款=(消费贷款+经营贷款)
    SELECT
     FSE.FIN_ORG_NO                                                          AS FIN_ORG_NO
    ,FSE.CCY                                                                 AS CCY
    ,'11'                                                                    AS BUSINESS_TYPE
    ,'普通贷款'                                                              AS TYPE_DESC
    ,FSE.TERM_CODE                                                           AS TERM_CODE        -- 期限代码
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219','F02201','F02202')AND DATA_DATE='#V_DATA_DATE#') FSE

UNION ALL

    -- 个人_消费贷款
    SELECT
     FSE.FIN_ORG_NO                                                                                  AS FIN_ORG_NO
    ,FSE.CCY                                                                                         AS CCY
    ,'111'                                                                                           AS BUSINESS_TYPE
    ,'消费贷款'                                                                                      AS TYPE_DESC
    ,FSE.TERM_CODE                                                                                   AS TERM_CODE        -- 期限代码
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02131','F02132','F0219')AND='#V_DATA_DATE#') FSE

    UNION ALL

    -- 个人_个人住房贷款+个人汽车消费贷款+其他消费贷款+经营贷款+助学贷款
    SELECT
     FSE.FIN_ORG_NO                                                                                      AS FIN_ORG_NO
    ,FSE.CCY                                                                                             AS CCY
    ,CASE WHEN FSE.BUSINESS_TYPE='F0211' THEN '1111' -- 个人住房贷款
          WHEN FSE.BUSINESS_TYPE='F0212' THEN '1112' -- 个人汽车消费贷款
          WHEN FSE.BUSINESS_TYPE='F0219' THEN '1114' -- 其他消费贷款
          WHEN FSE.BUSINESS_TYPE IN ('F02201','F02202') THEN '112'  -- 经营贷款
          WHEN FSE.BUSINESS_TYPE IN ('F02131','F02132') THEN '1113'  -- 助学贷款
     END                                                                                                 AS BUSINESS_TYPE
    ,CASE WHEN FSE.BUSINESS_TYPE='F0211' THEN '个人住房贷款'
          WHEN FSE.BUSINESS_TYPE='F0212' THEN '个人汽车消费贷款'
          WHEN FSE.BUSINESS_TYPE='F0219' THEN '其他消费贷款'
          WHEN FSE.BUSINESS_TYPE IN('F02201','F02202') THEN '经营贷款'
          WHEN FSE.BUSINESS_TYPE IN ('F02131','F02132') THEN '1113'  -- 助学贷款
     END                                                                                                 AS TYPE_DESC
    ,FSE.TERM_CODE                                                                                       AS TERM_CODE        -- 期限代码
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                                                        AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                                                           AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                                                           AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                                            AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                                                         AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                                                          AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                                                          AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                                                          AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='1' AND BUSINESS_TYPE IN ('F0211','F0212','F02201','F02202','F0219','F02131','F02132')AND DATA_DATE='#V_DATA_DATE#') FSE

UNION ALL

    -- 机构_普通贷款
    SELECT
     FSE.FIN_ORG_NO                                                          AS FIN_ORG_NO
    ,FSE.CCY                                                                 AS CCY
    ,'21'                                                                    AS BUSINESS_TYPE
    ,'普通贷款'                                                              AS TYPE_DESC
    ,FSE.TERM_CODE                                                           AS TERM_CODE        -- 期限代码
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F02201','F02202','F02203','F023')AND DATA_DATE='#V_DATA_DATE#') FSE

    UNION ALL

    -- 机构_贸易融资
    SELECT
     FSE.FIN_ORG_NO                                                          AS FIN_ORG_NO
    ,FSE.CCY                                                                 AS CCY
    ,'22'                                                                    AS BUSINESS_TYPE
    ,'贸易融资'                                                              AS TYPE_DESC
    ,FSE.TERM_CODE                                                           AS TERM_CODE        -- 期限代码
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F081','F082')AND DATA_DATE='#V_DATA_DATE#') FSE

    UNION ALL

    -- 机构_经营贷款+固定资产贷款+融资租赁+转贷款+并购贷款
    SELECT
     FSE.FIN_ORG_NO                                                          AS FIN_ORG_NO
    ,FSE.CCY                                                                 AS CCY
    ,CASE WHEN FSE.BUSINESS_TYPE='F02203' THEN '211'
          WHEN FSE.BUSINESS_TYPE='F023' THEN '212'
          WHEN FSE.BUSINESS_TYPE='F09'  THEN '23'
          WHEN FSE.BUSINESS_TYPE='F11'  THEN '24'
          WHEN FSE.BUSINESS_TYPE='F12'  THEN '25'
     END                                                                     AS BUSINESS_TYPE
    ,CASE WHEN FSE.BUSINESS_TYPE='F02203' THEN '经营贷款'
          WHEN FSE.BUSINESS_TYPE='F023' THEN '固定资产贷款'
          WHEN FSE.BUSINESS_TYPE='F09'  THEN '融资租赁'
          WHEN FSE.BUSINESS_TYPE='F11'  THEN '转贷款'
          WHEN FSE.BUSINESS_TYPE='F12'  THEN '并购贷款'
     END                                                                     AS TYPE_DESC
    ,FSE.TERM_CODE                                                           AS TERM_CODE        -- 期限代码
    ,COALESCE(FSE.OCCUR_AMOUNT,0)                                            AS OCCUR_AMOUNT     -- 贷款发生额
    ,COALESCE(FSE.AMOUNT_LM,0)                                               AS AMOUNT_LM        -- 发生额上期末
    ,COALESCE(FSE.AMOUNT_YS,0)                                               AS AMOUNT_YS        -- 当年累计发生额
    ,COALESCE(FSE.OCCUR_AMOUNT,0) - COALESCE(FSE.AMOUNT_YC,0)                AS AMOUNT_GROW      -- 同比增减
    ,COALESCE(FSE.WSUM_AMOUNT,0)                                             AS WSUM_AMOUNT      -- 发生额加权值
    ,COALESCE(FSE.WAMOUNT_LM,0)                                              AS WAMOUNT_LM       -- 发生额加权值上期末
    ,COALESCE(FSE.WAMOUNT_YS,0)                                              AS WAMOUNT_YS       -- 发生额加权值当年累计
    ,COALESCE(FSE.WAMOUNT_YC,0)                                              AS WAMOUNT_LC       -- 发生额加权值去年同期
    FROM (SELECT * FROM EDW.DS_LOAF_SUM WHERE CLIENT_TYPE='0' AND BUSINESS_TYPE IN ('F02203','F023','F09','F11','F12')AND DATA_DATE='#V_DATA_DATE#') FSE
  )T
 -- 与地区表最细级关联
 LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
 LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
 -- 关联贷款期限维度表
 LEFT JOIN DIMENSION.DIM_LOAN_TERM TERM ON T.TERM_CODE=TERM.LOAN_TERM_ID AND '#V_DATA_DATE#' BETWEEN TERM.START_DATE AND TERM.END_DATE
 GROUP BY AREA.AREA_CODE_4
         ,T.CCY
         ,T.BUSINESS_TYPE
         ,T.TYPE_DESC
         ,TERM.LOAN_TERM_ID
         ,TERM.LOAN_TERM_DSCR
;